﻿using System.Data;
using ContractManagementSystem.EntityObjects;

namespace ContractManagementSystem.DataAccessLayer
{
    public static class ContractorDao
    {
        #region"Get contractors"
        /// <summary>
        /// Get all contractor
        /// </summary>
        /// <returns></returns>
        public static DataTable SelectAllData()
        {
            var dataTable = CommonDao.GetDataTable("SP_Contractor_GetContractors");
            return dataTable;
        }
        /// <summary>
        /// Get contractor by id
        /// </summary>
        /// <param name="contractorId"></param>
        /// <returns></returns>
        public static DataTable GetContractorByContractorId(string contractorId)
        {
            var query ="SP_Contractor_GetContractorById @ContractorId='"+contractorId+"'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        /// <summary>
        /// Get contractor by user name
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static DataTable GetContractorByContractorUserName(string userName)
        {
            var query = "SP_Contractor_GetContractorByUserName @UserName='" + userName + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        /// <summary>
        /// Get contractor by role client
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public  static DataTable GetContractorByClient(string userName)
        {
            var dataTable = CommonDao.GetDataTable("sp_Contractor_GetContractorByClient @UserName='" + userName + "'");
            return dataTable;
        }
        /// <summary>
        /// Get contractor by role consultant
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static DataTable GetContractorByConsultant(string userName)
        {
            var dataTable = CommonDao.GetDataTable("sp_Contractor_GetContractorByConsultant @UserName='" + userName + "'");
            return dataTable;
        }

        #endregion

        #region"Search"
        /// <summary>
        /// Search contractor by fullname or email
        /// </summary>
        /// <param name="fullName"></param>
        /// <param name="email"></param>
        /// <returns></returns>
        public static DataTable SearchContractor(string fullName, string email)
        {
            var query = "sp_Contractor_GetContractorByFullNameAndEmail @FullName='" + fullName + "',"
                        + "@Email='" + email + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }

        public static DataTable SearchContractorByClient(string user, string fullName, string email)
        {
            var query = "[sp_Contractor_SearchContractorByClient] @UserName='" + user + "',"
                        + "@FullName='" + fullName + "',"
                        + "@Email='" + email + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        public static DataTable SearchContractorByConsultant(string user, string fullName, string email)
        {
            var query = "[sp_Contractor_SearchContractorByConsultant] @UserName='" + user + "',"
                        + "@FullName='" + fullName + "',"
                        + "@Email='" + email + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;            
        }
        #endregion

        #region"Insert/delete/update"

        public static string Insert(ContractorEntity contractor)
        {
            var sqlInsert = "SP_Contractor_InsertContractors "
                            + "@UserName='" + contractor.UserName + "',"
                            + "@FullName=N'" + contractor.FullName
                            + "'," + "@Email='" + contractor.Email
                            + "'," + "@PhoneNumber1='" + contractor.PhoneNumber1
                            + "'," + "@PhoneNumber2='" + contractor.PhoneNumber2
                            + "'," + "@Address=N'" + contractor.Address
                            + "'," + "@Branch=N'" + contractor.Branch
                            + "'," + "@SortCode='" + contractor.SortCode + "'";
            return CommonDao.ExecuteSqlCommand(sqlInsert);

        }

        public static string Update(ContractorEntity contractor)
        {
            var sqlUpdate = "SP_Contractor_UpdateContractor "
                            + "@UserName='" + contractor.UserName + "',"
                            + "@ContractorId='" + contractor.ContractorId + "',"
                            + "@FullName=N'" + contractor.FullName
                            + "'," + "@Email='" + contractor.Email
                            + "'," + "@PhoneNumber1='" + contractor.PhoneNumber1
                            + "'," + "@PhoneNumber2='" + contractor.PhoneNumber2
                            + "'," + "@Address=N'" + contractor.Address
                            + "'," + "@Branch=N'" + contractor.Branch
                            + "'," + "@SortCode='" + contractor.SortCode + "'";
            return CommonDao.ExecuteSqlCommand(sqlUpdate);

        }

        public static string Delete(ContractorEntity contractor)
        {
            var sqlDelete = "SP_Contractor_DeleteContractors @ContractorId='" + contractor.ContractorId + "'";
            return CommonDao.ExecuteSqlCommand(sqlDelete);
        }

        #endregion

        //check exists email
        public static bool CheckExistEmailValue(string value)
        {
            string sql = "SELECT Email FROM tblContractors WHERE Email = '" + value + "'";
            DataTable dataTable = CommonDao.GetDataTable(sql);
            if (dataTable.Rows.Count > 0)
                return true;
            return false;
        }
    }
}
